﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class PhieuThueDAO : ConnectionDAO
    {
        public static long LayMaCTPT(long mP)
        {
            long mCTPT = -1;
            OleDbConnection cN = null;

            try
            {
                cN = Cnect();

                string cmString = @"SELECT CT_PT.MaCT_PT
                                    FROM PHIEUTHUE PT,CHITIET_PT CT_PT
                                    WHERE PT.MaPhieuThue=CT_PT.MaPhieuThue AND PT.MaPhong=@mP";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@mP", OleDbType.VarChar);
                thamSo.Value = mP;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        mCTPT = rDer.GetInt32(0);
                }
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return mCTPT;
        }

        public static long LayMaPTHienTai(long mP)
        {
            long mPT = 0;
            OleDbConnection cN = null;

            try
            {

                cN = Cnect();

                string cmString = @"SELECT MAX(PT.MaPhieuThue)
                                    FROM PHIEUTHUE PT
                                    WHERE PT.MaPhong=@mP
                                    ";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;


                thamSo = new OleDbParameter("@mP", OleDbType.Integer);
                thamSo.Value = mP;
                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        mPT = rDer.GetInt32(0);
                }
            }
            catch (Exception ex)
            {
                mPT = 0;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return mPT;
        }

        public static PhieuThueDTO LayPTTheoMPT(long mPT)
        {
            PhieuThueDTO PT = new PhieuThueDTO();
            OleDbConnection cN = null;

            try
            {

                cN = Cnect();

                string cmString = @"SELECT PT.MaPhieuThue,PT.MaPhong,PT.NgayThue
                                    FROM PHIEUTHUE PT
                                    WHERE PT.MaPhieuThue=@mPT
                                    ";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;


                thamSo = new OleDbParameter("@mPT", OleDbType.Integer);
                thamSo.Value = mPT;
                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        PT.MaPhieuThue = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        PT.MaPhong.MaPhong = rDer.GetInt32(1);
                    if (!rDer.IsDBNull(2))
                        PT.NgayThue = rDer.GetDateTime(2);
                }
            }
            catch (Exception ex)
            {
                PT = new PhieuThueDTO();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return PT;
        }

        public static bool ThemPT(long mP, DateTime dt)
        {
            bool kq = true;
            OleDbConnection cN = null;

            try
            {

                cN = Cnect();

                string cmString = @"INSERT INTO PHIEUTHUE(MaPhong,NgayThue) VALUES (@mP,@dt)";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;


                thamSo = new OleDbParameter("@mP", OleDbType.Integer);
                thamSo.Value = mP;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@dt", OleDbType.Date);
                thamSo.Value = dt;
                cM.Parameters.Add(thamSo);

                cM.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                kq = false;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return kq;
        }
    }
}
